﻿using Common;
using Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using Dapper;
using System.Linq;
using System.Text;
using System.Xml.Linq;

namespace DbControl
{
    public class OprDeviceAlarmConfig
    {
        #region DevicesAlarmByConfig        
        /// <summary>
        /// 模糊查找
        /// </summary>
        /// <param name="Name"></param>
        /// <returns></returns>
        public static DataTable GetdevicesAlarmConfigByName(string Name)
        {
            using (IDbConnection connection = new SqlConnection(OprSql.connectString))
            {
                string sql = "";
                if (string.IsNullOrEmpty(Name))
                {
                    sql = @"SELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_ids 
                          ,A.device_code 
                          ,A.device_name  
                          ,A.device_position
                          ,C.oil_code
                          ,C.oil_name                         
                          ,A.flag 
                         FROM  devices A  left join                        
                         oils C on   A.oil_id=C.id  where A.parentid>0 AND A.flag>0";
                }
                else
                {
                    sql = string.Format(@"SSELECT A.id
                          ,A.parentid 
	                      ,A.sensor_id 
                          ,A.parameter_id 
                          ,A.warn_ids 
                          ,A.device_code 
                          ,A.device_name  
                          ,A.device_position
                          ,C.oil_code
                          ,C.oil_name                       
                          ,A.flag 
                         FROM  devices A  left join                       
                         oils C on   A.oil_id=C.id  where A.parentid>0 AND A.flag>0 AND A.device_name like '%{0}%'", Name);
                }
                DataTable dt = new DataTable();
                dt.Load(connection.ExecuteReader(sql));
                return dt;
            }
        }
        /// <summary>
        /// 根据id 或者 parentid 查找其信息
        /// </summary>
        /// <param name="parentId"></param>
        /// <param name="isParent"></param>
        /// <returns></returns>
        public static DataTable GetdevicesByTreeNode(int parentId, bool isParent)
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = "";
                    if (isParent)
                    {
                        sql = @"SELECT A.id
                                ,A.parentid 
	                            ,A.sensor_id 
                                ,A.parameter_id 
                                ,A.warn_ids 
                                ,A.device_code 
                                ,A.device_name 
                                ,A.device_position
                                ,C.oil_code
                                ,C.oil_name
                                ,A.flag 
                            FROM  devices A  left join                          
                            oils C on   A.oil_id=C.id  where A.flag>0 AND  A.id In (SELECT id FROM devices WHERE parentid=@parentid)";
                    }
                    else
                    {
                        sql = @"SELECT A.id
                                ,A.parentid 
	                            ,A.sensor_id 
                                ,A.parameter_id 
                                ,A.warn_ids 
                                ,A.device_code 
                                ,A.device_name 
                                ,A.device_position
                                ,C.oil_code
                                ,C.oil_name
                                ,A.flag 
                            FROM  devices A  left join                          
                            oils C on   A.oil_id=C.id Where A.flag>0  AND  A.id=@parentid";
                    }
                    dt.Load(connection.ExecuteReader(sql, new { parentid = parentId }));
                }
            }
            catch (Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
              
        /// <summary>
        /// 设备树
        /// </summary>
        /// <returns></returns>
        public static DataTable GetdevicesTree()
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT [id]
                                ,[parentid]     
                                ,[device_name]    
                                ,[flag]
                                FROM [devices] where flag>0";

                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        /// <summary>
        /// 根据父节点查找其子节点设备
        /// </summary>
        /// <param name="parentId"></param>
        /// <returns></returns>
        public static DataTable GetdevicesByParentId(int parentId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT SELECT A.id
                                ,A.parentid 
	                            ,A.sensor_id 
                                ,A.parameter_id 
                                ,A.warn_id 
                                ,A.device_code 
                                ,A.device_name 
                                ,A.device_position
                                ,B.sensor_code
                                ,B.sensor_name
                                ,B.sensor_sn
                                ,A.oil_id     
                                ,C.oil_code
                                ,C.oil_name
                                ,A.flag 
                            FROM  devices A  left join
                            sensors B on A.sensor_id =B.id left join 
                            oils C on   A.oil_id=C.id Where A.flag>0 AND  A.parentid=@parentid ";

                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {

                lLogWrite.WriteLog("加载设备列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        public static DataTable GetAllRules()
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {                   
                    string sql = @"SELECT A.id 
                                  ,A.rule_code 
                                  ,A.sensor_id 
                                  ,A.parameter_id 
                                  ,B.parameter_name
                                  ,A.upper_upper_value 
                                  ,A.upper_value 
                                  ,A.lower_value 
                                  ,A.lower_lower_value 
                                  ,A.flag 
                              FROM rules A  left join parameters B on A.parameter_id=B.id where A.flag>0 ";
                    sql = string.Format(sql); ;
                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog("加载规则列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        public static DataTable GetRules(string ids)
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    if (string.IsNullOrEmpty(ids))
                        ids = "0";
                    string sql = @"SELECT A.id 
                                  ,A.rule_code 
                                  ,A.sensor_id 
                                  ,A.parameter_id 
                                  ,B.parameter_name
                                  ,A.upper_upper_value 
                                  ,A.upper_value 
                                  ,A.lower_value 
                                  ,A.lower_lower_value 
                                  ,A.flag 
                              FROM rules A  left join parameters B on A.parameter_id=B.id where A.flag>0 AND A.id in ({0})";
                    sql=string.Format(sql, ids);;
                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog("加载规则列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        /// <summary>
        /// 获取油品列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetConfigRules()
        {
            DataTable dt = new DataTable();
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"SELECT id 
                                  ,rule_code       
                                  ,parameter_id 
                                  ,upper_upper_value 
                                  ,upper_value 
                                  ,lower_value 
                                  ,lower_lower_value 
                                  ,flag 
                              FROM  rules  where  flag>0";
                    dt.Load(connection.ExecuteReader(sql));
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog("加载规则列表出错!" + ex.Message.ToString());
                dt = null;
            }
            return dt;
        }
        /// <summary>
        /// 关联设备的规则ID
        /// </summary>
        /// <param name="deviceId"></param>
        /// <param name="sensorId"></param>
        /// <returns></returns>
        public static bool SetRules(int deviceId, string sensorId)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE devices  SET warn_ids = @warn_ids  WHERE id=@id ";
                    intNum = (int)connection.Execute(sql, new { id = deviceId, warn_ids = sensorId });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }
            return false;
        }
        public static bool ClearRules(int deviceId)
        {
            int intNum = 0;
            try
            {
                using (IDbConnection connection = new SqlConnection(OprSql.connectString))
                {
                    string sql = @"UPDATE devices  SET warn_ids = null  WHERE id=@id ";
                    intNum = (int)connection.Execute(sql, new { id = deviceId });
                }
            }
            catch (Exception ex)
            {
                lLogWrite.WriteLog(ex.ToString());
            }
            if (intNum > 0)
            {
                return true;
            }
            return false;
        }

        #endregion
    }
}
